﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace QLK.Database.DALs
{
    public class ProductDAL
    {
        private ManageStoreDataContext db;

        public ProductDAL()
        {
            db = new ManageStoreDataContext();
        }

        public Product ProductGetByID(int productID)
        {
            Product product = db.Products.SingleOrDefault<Product>(pro => pro.ProductID == productID);
            return product;
        }

        public List<Product> ProductGetAll()
        {
            var products = from pro in db.Products
                           orderby pro.ProductName
                           select pro;

            return products.ToList<Product>();
        }

        public List<Product_GetForSearchResult> ProductGetForGrid()
        {
            var products = db.Product_GetForSearch();

            return products.ToList<Product_GetForSearchResult>();
        }

        public bool ProductDeleteByID(int productID)
        {
            Product product = db.Products.SingleOrDefault<Product>(pro => pro.ProductID == productID);

            if (product == null)
                return false;
            db.Products.DeleteOnSubmit(product);
            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        public bool ProductCheckDuplicateUPC(string UPC)
        {
            var Pros = from pro in db.Products
                       select pro;

            if (Pros.ToList<Product>().Count == 0)
                return true;
            else
            {
                Product product = db.Products.SingleOrDefault<Product>(pro => pro.UPC.Equals(UPC));

                if (product == null)
                    return true;
                return false;
            }
        }

        public bool ProductUpdate(Product Prods)
        {
            Product product = db.Products.SingleOrDefault<Product>(pro => pro.ProductID == Prods.ProductID);

            if (product == null)
                return false;

            product.UPC = Prods.UPC;
            product.ProductName = Prods.ProductName;
            product.CategoryID = Prods.CategoryID;
            product.Manufacture = Prods.Manufacture;
            product.Image = Prods.Image;

            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        public bool ProductInsert(Product Prods)
        {
            try
            {
                db.Products.InsertOnSubmit(Prods);
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        public List<Product_GetForSearchByProductNameResult> ProductGetByProductName(string ProName)
        {
            var Pro = db.Product_GetForSearchByProductName(ProName);

            if (Pro == null)
                return null;
            else
                return Pro.ToList<Product_GetForSearchByProductNameResult>();
        }

        public List<Product_GetForSearchByUPCResult> ProductGetByUPC(string UPC)
        {
            var Pro = db.Product_GetForSearchByUPC(UPC);

            if (Pro == null)
                return null;
            else
                return Pro.ToList<Product_GetForSearchByUPCResult>();
            
        }

        public List<Product_GetForSearchByCategoryIDResult> ProductGetByCateID(int CateID)
        {
            var Pro = db.Product_GetForSearchByCategoryID(CateID);

            if (Pro == null)
                return null;
            else
                return Pro.ToList<Product_GetForSearchByCategoryIDResult>();           
        }

        public List<Product_GetForSearchByManuIDResult> ProductGetByManuID(int ManuID)
        {
            var Pro = db.Product_GetForSearchByManuID(ManuID);

            if (Pro == null)
                return null;
            else
                return Pro.ToList<Product_GetForSearchByManuIDResult>();
        }

        public List<Product_GetForSearchByPriceResult> ProductGetByPrices(int PriceFrom, int PriceTo)
        {
            var Pro = db.Product_GetForSearchByPrice(PriceFrom, PriceTo);

            if (Pro == null)
                return null;
            else
                return Pro.ToList<Product_GetForSearchByPriceResult>();
        }
    }
}
